﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using Maticsoft.DBUtility;
using System.Data;
using System.Collections;

namespace SkWeb.FAXZ
{
    public partial class ExcelBrowse : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Excel.Application excel = new Excel.Application();  
            //获取excel启动结束的时间 
            try
            {
                if (!IsPostBack)
                {
                    string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
                    string strJing_id = Request.QueryString["id"];//模板ID
                    Session["tempalteid"] = strTemplate_id;
                    Session["jing_id"] = strJing_id;
                    string strSql = "select tmp_mc from tb_template_menu t where tmp_id=" + strTemplate_id;
                    string strFileName = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();

                    FpSpread2.OpenExcel(Server.MapPath("~/") + "Files/"+strFileName+".xls");
                    FpSpread2.ActiveSheetView.PageSize = 49;
                    FpSpread2.Columns.Count = 17;
                    FpSpread2.EditModeReplace = true;
                    fileExcecDoc(Server.MapPath("~/") + "Files/" +strFileName + "_img");
                    genExcelDoc(strJing_id, strTemplate_id);
                    // FpSpread2.ActiveSheetView.AutoPostBack = true;
                    FarPoint.Web.Spread.ComboBoxCellType cb = new FarPoint.Web.Spread.ComboBoxCellType();//new string[] { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k" }, new string[] { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k" });
                    BindDictItems(cb, "");
                    cb.ShowButton = true;
                    List<Pos> listPos = new List<Pos>();
                    
                    int y = FpSpread2.Rows.Count;
                    Session["pos"] = listPos;
                    strSql = "select content,row_num from tb_hyperlink ";//where fa_id= ";
                    DataTable dt = OracleHelper.Query(strSql).Tables[0];
                    ListBox1.DataSource = dt;
                    ListBox1.DataTextField = "CONTENT";
                    ListBox1.DataValueField = "ROW_NUM";
                    ListBox1.DataBind();
                 
                }
            }
            catch (Exception ex)
            {
            }

        }

        /// <summary>
        /// 给excel表格填充图片
        /// </summary>
        /// <param name="path"></param>
        public void fileExcecDoc(string path)
        {
           // FileInfo fileInfo = new FileInfo(path);

            DirectoryInfo theFolder = new DirectoryInfo(path);

            foreach (FileInfo info in theFolder.GetFiles())
            {
                FarPoint.Web.Spread.ImageCellType img = new FarPoint.Web.Spread.ImageCellType("../Files/" + theFolder.Name + "/" + info.Name);
        
                string str =info.Name.Remove(info.Name.IndexOf('.'));

                string[] para = str.Split('-');
                int top = Convert.ToInt32(para[0])-1;
                int bottom = Convert.ToInt32(para[1])-1;
                if(para.Length==2)
                {
                    FpSpread2.Cells[top, 0].ColumnSpan = 9;
                    FpSpread2.Cells[top, 0].RowSpan = bottom-top+1;
                    FpSpread2.Cells[top, 0].CellType = img;

                }
                else if (para.Length == 3)
                {
                    string strCol = para[2];
                    int y1 = strCol[0] - 'a';
                    int y2 = strCol[1] - 'a';

                    FpSpread2.Cells[top, y1].ColumnSpan = y2-y1+1;
                    FpSpread2.Cells[top, y1].RowSpan = bottom - top + 1;
                    FpSpread2.Cells[top, y1].CellType = img;
                }

            }

   


        }
        /// <summary>
        /// 从数据库中
        /// </summary>
        /// <param name="strJingId"></param>
        /// <param name="strTemplateId"></param>
        public void genExcelDoc(string strJingId, string strTemplateId)
        {
            try
            {
                FpSpread2.Cells[46, 3].Text = DateTime.Now.ToString("yyyy年MM月dd日");
                string strSql = "select * from tb_bookmark where faid =" + strTemplateId;
                DataTable dtBM = OracleHelper.Query(strSql).Tables[0];
                DataColumn dc = new DataColumn("VALUE", Type.GetType("System.String"));
                dtBM.Columns.Add(dc);
                FillTable(dtBM, strJingId);
                foreach (DataRow dr in dtBM.Rows)
                {
                    if(Convert.IsDBNull(dr["X"])||Convert.IsDBNull(dr["Y"]))
                        continue;

                    int x = Convert.ToInt32(dr["X"].ToString()) - 1;
                    int y = Convert.ToInt32(dr["Y"].ToString()) - 1;
                    this.FpSpread2.ActiveSheetView.Cells[x, y].Text = dr["Value"].ToString();
                    // tempDocument.Range.Bookmarks[dr["NAME"].ToString()].Text = dr["Value"].ToString();
                }
            }
            catch (Exception ex)
            {

            }

        }

        void FillTable(DataTable dt, string pkValue)
        {
            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string strFormula = dr["FORMULA"].ToString();
                    if (string.IsNullOrEmpty(strFormula.Trim()))
                    {
                        string name = dr["NAME"].ToString();
                        string text = dr["TEXT"].ToString();
                        string tablename = dr["TABLENAME"].ToString();
                        string fieldname = dr["FIELDNAME"].ToString();
                        string pkfield = dr["PKFIELD"].ToString();
                        string strSql = "select " + fieldname + " from " + tablename + " where " + pkfield + "='" + pkValue + "' ";
                        string strValue = "";
                       
                        DataTable dt_tmp =OracleHelper.Query(strSql).Tables[0];
                        if (dt_tmp.Rows.Count>0)
                        {
                            strValue = dt_tmp.Rows[0][0].ToString();
                        }
                        dr["VALUE"] = strValue;
                    }
                    else
                    {
                        DataTable dtFormula = OracleHelper.Query("select * from tb_formula where name='" + strFormula + "' ").Tables[0];
                        string pktable = dtFormula.Rows[0]["PKTABLENAME"].ToString();
                        string pkfield = dtFormula.Rows[0]["PKFIELDNAME"].ToString();
                        string Content = dtFormula.Rows[0]["VALUE"].ToString();

                        DataTable dtVars = OracleHelper.Query("select * from tb_formula_var where name ='" + strFormula + "' ").Tables[0];
                        foreach (DataRow drVar in dtVars.Rows)
                        {
                            string varName = drVar["BKNAME"].ToString();
                            string strTable = drVar["TABLENAME"].ToString();
                            string strField = drVar["FIELDNAME"].ToString();
                            string strpkField = drVar["PKFIELDNAME"].ToString();
                            string strVarValue = OracleHelper.Query(" select " + strField + " from " + strTable + " where " + strpkField + "='" + pkValue + "' ").Tables[0].Rows[0][0].ToString();
                            Content = Content.Replace(varName, strVarValue);
                        }
                        CodeDriver driver = new CodeDriver();
                        bool isError;
                        string strResult = driver.ComplileAndRun(Content, out isError);//调用执行代码的类
                        dr["VALUE"] = strResult;
                    }
                }
                dt.AcceptChanges();
            }
            catch (Exception ex)
            {

            }

        }

        protected void FpSpread2_CellClick(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
        {

            int x = ((System.Drawing.Point)(((System.Web.UI.WebControls.CommandEventArgs)(e)).CommandArgument)).X;
            int y = ((System.Drawing.Point)(((System.Web.UI.WebControls.CommandEventArgs)(e)).CommandArgument)).Y;

            FarPoint.Web.Spread.ComboBoxCellType cb = new FarPoint.Web.Spread.ComboBoxCellType();//new string[] { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k" }, new string[] { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k" });
            BindDictItems(cb, "");

            cb.ShowButton = true;

            FpSpread2.Sheets[0].Cells[x, y].CellType = cb;
            FpSpread2.SaveChanges();

        }

        /// <summary>
        /// 绑定下拉列表控件为指定的数据字典列表
        /// </summary>
        /// <param name="combo">下拉列表控件</param>
        /// <param name="dictTypeName">数据字典类型名称</param>
        public static void BindDictItems(FarPoint.Web.Spread.ComboBoxCellType combo, string dictTypeName)
        {
            // combo.AutoPostBack = true;
            // Dictionary<string, string> dict = BLLFactory<DictData>.Instance.GetDictByDictType(dictTypeName);
            List<string> listData = new List<string>();
            listData.Add("");
            listData.Add("dddd");
    
            combo.Items = listData.ToArray();
            //combo.Editable = true;
        }
        public struct Pos
        {
            public int x;
            public int y;
            public string bookMark;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            FpSpread2.SaveChanges();
            List<Pos> listNew = new List<Pos>();
            List<Pos> listPos = new List<Pos>();
            listPos = (List<Pos>)Session["pos"];
            Pos posNew = new Pos();
            foreach (Pos pos in listPos)
            {

                posNew.x = pos.x;
                posNew.y = pos.y;
                posNew.bookMark = FpSpread2.Cells[pos.x, pos.y].Text;
                listNew.Add(posNew);
            }
            Session["pos"] = listNew;

        }

      
        protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ListBox box = sender as ListBox;
            int row_num = Convert.ToInt32(box.SelectedValue);
            FpSpread2.GotoPage(row_num / 49);
            //if (Session["pre"] != null)
            //{
            //    int preRow =(int) Session["pre"];
            //    FpSpread2.Rows[preRow - 1].BackColor = System.Drawing.Color.White;
            //}
            //FpSpread2.Rows[row_num - 1].BackColor = System.Drawing.Color.YellowGreen;
            //Session["pre"] = row_num;
        }

        protected void btnSubmitClicked(object sender, EventArgs e)
        {
            FpSpread2.SaveChanges();
            string strSql = "Select column_name,data_type From user_tab_columns where table_name = 'ZYSGSJ'";

            string strFaId = Session["tempalteid"].ToString();
                    string strid = Session["jing_id"].ToString();

            strSql = "select t.tablename, t.fieldname,t.x,t.y, s.data_type from tb_bookmark t,user_tab_columns s where s.TABLE_NAME=t.tablename and s.COLUMN_NAME=t.fieldname and  t.faid= "+strFaId;

            DataTable dt = OracleHelper.Query(strSql).Tables[0];


            ArrayList listSql = new ArrayList();
            string strFormat=" update {0} set {1} ={2} where id = "+strid;
           
            //string 
            foreach (DataRow dr in dt.Rows)
            {
                if (Convert.IsDBNull(dr["X"]) || Convert.IsDBNull(dr["Y"]) || Convert.IsDBNull(dr["TABLENAME"]) || Convert.IsDBNull(dr["FIELDNAME"]))
                {
                    continue;
                }

                int x = Convert.ToInt32(dr["X"])-1;
                int y = Convert.ToInt32(dr["Y"])-1;
                string strVal= FpSpread2.Cells[x, y].Text.Trim();
                string strType = Convert.ToString(dr["DATA_TYPE"]);

                switch (strType)
                {

                    case "VARCHAR2":
                        strVal = "'" + strVal + "'";
                        break;
                    case "DATE" :
                        strVal = " to_date('" + strVal + "','yyyy-mm-dd') ";
                        break; 
                }
                if (strVal.Length < 1)
                    continue;

                listSql.Add(string.Format(strFormat, Convert.ToString(dr["TABLENAME"]), Convert.ToString(dr["FIELDNAME"]),strVal));
               // string strSql = "update " + Convert.ToString(dr["TABLENAME"]) +" set "+ Convert.ToString(dr["FIELDNAME"]) +" = "+ strVal +" where ";
              

            }
            OracleHelper.ExecuteSqlTran(listSql);


        }

    }
}